package ex.com.jm.food.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;









import ex.com.jm.food.dao.UserCompanyDao;
import ex.com.jm.food.entity.Company;

public class UserCompanyDaoImpl extends HibernateDaoSupport implements
		UserCompanyDao {

	public UserCompanyDaoImpl() {
		// TODO Auto-generated constructor stub
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<Company> getCompanies(final int initPage,final int pageRow,
			final String address,final String comName,final Integer comType,final Integer comDistr,
			final String comDiscount,final String order,final String sort) {
		// TODO Auto-generated method stub
		Object companies=this.getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				/***
				 * 主页显示的有企业名称，企业头像，企业总体评价，企业每月总销售额，企业配送要求，平均送餐时间，优惠活动
				 * 查询的字段还有企业id
				 */
				String sql="select "
						+ "com.com_id as comId,"
						+ "com.com_name as comName,"
						+ "com.com_image as comImage,"
						+ "com.com_distr as comDis,"
						+ "cps.total_num as totalNum,"
						+ "cps.room_date as roomDate, "
						+ "cps.avg_eval as avgEval"
						+ " from jm_company as com "
						+ " left join jm_company_profit_summary as cps on com.com_id=cps.com_id "
						+ " where com.status=1  and com.com_address like '%"+address+"%' ";
				if(comName!=null&&!comName.equals("")){
					sql+=" and com.com_name like '%"+comName+"%'";
				}
				if(comType!=null&&!comType.equals("")){
					sql+=" and com.comtype_id="+comType;
				}
				if(comDistr!=null&&!comDistr.equals("")){
					System.err.println(comDistr);
					if(comDistr==0){
						System.out.println(comDistr);
						sql+=" and com.com_distr<=10";//起送价格10元以下
					}
					if(comDistr==1){
						sql+=" and com.com_distr<=20";//起送价格20元以下
					}
					if(comDistr==2){
						sql+=" and com.com_distr<=30";//起送价格20元以下
					}
				}
				if(comDiscount!=null&&!comDiscount.equals("")){
					//优惠活动
					sql+=" and com_discount like '%"+comDiscount+"%'";
				}
				if(order!=null&&!order.equals("")&&sort!=null&&!sort.equals("")){
					sql+=" order by cps."+order+" "+sort;//评价，销量，送餐速度
				}else{
					sql+=" order by cps.total_num desc";
				}
				Query query=session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setFirstResult(initPage).setMaxResults(pageRow);
				return query.list();
			}
		});
		return (List<Company>) companies;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public int comCount(final String address, final String comName,final Integer comType,final Integer comDistr,
			final String comDiscount) {
		// TODO Auto-generated method stub
		int count=this.getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				String hql="from Company com where com.status=1 and com.comAddress like '%"+address+"%' ";
				if(comName!=null&&!comName.equals("")){
					hql+=" and com.comName like '%"+comName+"%'";
				}
				if(comType!=null&&!comType.equals("")){
					hql+=" and com.comtypeId="+comType;
				}
				if(comDistr!=null&&!comDistr.equals("")){
					if(comDistr==0){
						hql+=" and com.comDistr<=10";//起送价格10元以下
					}
					if(comDistr==1){
						hql+=" and com.comDistr<=20";//起送价格20元以下
					}
					if(comDistr==2){
						hql+=" and com.comDistr<=30";//起送价格20元以下
					}
				}
				if(comDiscount!=null&&!comDiscount.equals("")){
					//优惠活动
					hql+=" and com.comDisCount like'%"+comDiscount+"%'";
				}
				Query query=session.createQuery(hql);
				return query.list().size();
			}
		});
		return count;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<Company> getCompany(final String comId) {
		// TODO Auto-generated method stub
		Object company=this.getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				String sql="select com.com_id as comId,"
						+ " com.com_name as comName,"
						+ " comtype.comtype_name as comtypeName,"
						+ " com.com_discount as comDisCount,"
						+ " notice.notice_content as noticeContent"
						+ " from jm_company as com "
						+ " left join jm_notice as notice on com.com_id=notice.com_id "
						+ " join jm_companytype as comtype on com.comtype_id=comtype.comtype_id "
						+ " where com.com_id='"+comId+"'";
				Query query=session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).setFirstResult(0).setMaxResults(1);
				return query.list();
			}
		});
		return  (List<Company>) company;
	}

}
